package org.thinkinbunny.financial.data;

import java.io.InputStream;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.thinkinbunny.financial.http.HttpTools;
import org.thinkinbunny.financial.util.DLog;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SyncHelper {
	DbHelper dbHelper;
	SQLiteDatabase db;
	Cursor cursor;

	public SyncHelper(Context context) {
		super();
		// TODO Auto-generated constructor stub
		dbHelper = new DbHelper(context, DbHelper.DbName);

	}

	public boolean SyncDataFromServer(JSONArray updateMainTable,
			JSONArray updateAccountTable, JSONArray updateProjectTable,
			JSONArray updateTypeTable) {
		db = dbHelper.getWritableDatabase();
		// 采用事务处理，方式数据不一致发生数据丢失
		db.beginTransaction();
		try {
			db.execSQL("delete from accountInfo");
			db.execSQL("delete from accountTable");
			db.execSQL("delete from typeTable");
			db.execSQL("delete from projectTable");
			ContentValues contentValues = new ContentValues();
			{// 更新 Account表

				for (int i = 0; i < updateAccountTable.length(); i++) {
					JSONObject jsonObject = updateAccountTable.getJSONObject(i);
					DLog.i("SyncHelper>>SyncDataFromServer",
							jsonObject.toString());
					// [{"billType":"打的","project":"按摩俊","balance":"5999"
					// ,"time":"14:38","accountname":"支付宝","date":"2012-3-10","store":"按摩俊"},

					contentValues.put("account",
							jsonObject.getString("account"));

					db.insert("accountTable", null, contentValues);
					contentValues.clear();

				}
			}
			{// 更新 Project表

				for (int i = 0; i < updateProjectTable.length(); i++) {
					JSONObject jsonObject = updateProjectTable.getJSONObject(i);
					DLog.i("SyncHelper>>SyncDataFromServer",
							jsonObject.toString());
					// [{"billType":"打的","project":"按摩俊","balance":"5999"
					// ,"time":"14:38","accountname":"支付宝","date":"2012-3-10","store":"按摩俊"},

					contentValues.put("project",
							jsonObject.getString("project"));

					db.insert("projectTable", null, contentValues);
					contentValues.clear();

				}
			}
			{// 更新 Type表

				for (int i = 0; i < updateTypeTable.length(); i++) {
					JSONObject jsonObject = updateTypeTable.getJSONObject(i);
					DLog.i("SyncHelper>>SyncDataFromServer",
							jsonObject.toString());
					// [{"billType":"打的","project":"按摩俊","balance":"5999"
					// ,"time":"14:38","accountname":"支付宝","date":"2012-3-10","store":"按摩俊"},

					contentValues.put("type", jsonObject.getString("type"));

					db.insert("typeTable", null, contentValues);
					contentValues.clear();

				}
			}

			{// 更新 主表
				// ContentValues contentValues=new ContentValues();
				for (int i = 0; i < updateMainTable.length(); i++) {
					JSONObject jsonObject = updateMainTable.getJSONObject(i);
					DLog.i("SyncHelper>>SyncDataFromServer",
							jsonObject.toString());
					// [{"billType":"打的","project":"按摩俊","balance":"5999"
					// ,"time":"14:38","accountname":"支付宝","date":"2012-3-10","store":"按摩俊"},
					contentValues.put("balance",
							Float.parseFloat(jsonObject.getString("balance")));
					contentValues.put("billType",
							jsonObject.getString("billType"));
					contentValues.put("accountname",
							jsonObject.getString("accountname"));
					contentValues.put("store", jsonObject.getString("store"));
					contentValues.put("project",
							jsonObject.getString("project"));
					contentValues.put("date", jsonObject.getString("date"));
					contentValues.put("time", jsonObject.getString("time"));
					db.insert("accountInfo", null, contentValues);
					contentValues.clear();

				}
			}

			db.setTransactionSuccessful();

		} catch (Exception e) {
			db.close();
			// db.endTransaction();
			return false;

			// TODO: e exception
		}
		db.endTransaction();
		db.close();
		db = null;
		return true;

	}

	public JSONArray getJsonArrayFromDB() {
		JSONArray jsonArray = new JSONArray();

		// "balance decimal(10,2) DEFAULT 0," + "billType text, "
		// + "accountname text, " + "store text, " + "project text, "
		// + "date text, " + "time time);";
		db = dbHelper.getReadableDatabase();
		cursor = db.rawQuery("select * from accountInfo", null);
		while (cursor.moveToNext()) {
			try {
				JSONObject billoObject = new JSONObject();
				billoObject.put("billType",
						cursor.getString(cursor.getColumnIndex("billType")));
				billoObject.put("balance",
						cursor.getFloat(cursor.getColumnIndex("balance")));
				billoObject.put("accountname",
						cursor.getString(cursor.getColumnIndex("accountname")));
				billoObject.put("store",
						cursor.getString(cursor.getColumnIndex("store")));
				billoObject.put("project",
						cursor.getString(cursor.getColumnIndex("project")));
				billoObject.put("date",
						cursor.getString(cursor.getColumnIndex("date")));
				billoObject.put("time",
						cursor.getString(cursor.getColumnIndex("time")));
				jsonArray.put(billoObject);

			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		DLog.i("SyncHelper_>>", "读取数据库为json" + jsonArray.toString());
		cursor.close();
		db.close();
		dbHelper.close();
		return jsonArray;

	}

	public JSONArray getJsonArrayFromDB_AccountTable() {
		JSONArray jsonArray = new JSONArray();

		db = dbHelper.getReadableDatabase();
		cursor = db.rawQuery("select * from accountTable", null);
		while (cursor.moveToNext()) {
			try {
				JSONObject billoObject = new JSONObject();

				billoObject.put("account",
						cursor.getString(cursor.getColumnIndex("account")));
				jsonArray.put(billoObject);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		DLog.i("SyncHelper_>>", "读取数据库为json" + jsonArray.toString());
		cursor.close();
		db.close();
		dbHelper.close();
		return jsonArray;

	}

	public JSONArray getJsonArrayFromDB_ProjectTable() {
		JSONArray jsonArray = new JSONArray();

		db = dbHelper.getReadableDatabase();
		cursor = db.rawQuery("select * from projectTable", null);
		while (cursor.moveToNext()) {
			try {
				JSONObject billoObject = new JSONObject();
				billoObject.put("project",
						cursor.getString(cursor.getColumnIndex("project")));
				jsonArray.put(billoObject);
				// billoObject=null;
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		DLog.i("SyncHelper_>>", "读取数据库为json" + jsonArray.toString());
		cursor.close();
		db.close();
		dbHelper.close();
		return jsonArray;

	}

	public JSONArray getJsonArrayFromDB_TypeTable() {
		JSONArray jsonArray = new JSONArray();

		db = dbHelper.getReadableDatabase();
		cursor = db.rawQuery("select * from typeTable", null);
		while (cursor.moveToNext()) {
			try {
				JSONObject billoObject = new JSONObject();
				billoObject.put("type",
						cursor.getString(cursor.getColumnIndex("type")));
				jsonArray.put(billoObject);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		DLog.i("SyncHelper_>>", "读取数据库为json" + jsonArray.toString());
		cursor.close();
		db.close();
		dbHelper.close();
		return jsonArray;

	}

	public JSONArray setJsonArrayFromServer(InputStream inputStream) {
		// JSONArray jsonArray=new JSONArray(inputStream.)
		String jsonSource = HttpTools.stream2string(inputStream);
		JSONArray jsonArray = null;
		try {
			jsonArray = new JSONArray(jsonSource);
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jsonArray;

	}

}
